# Oracle连接优化实施脚本
# 用于快速实施Oracle连接框架优化方案

param(
    [Parameter(Mandatory=$false)]
    [ValidateSet("optimize-current", "add-ef-core", "performance-test", "all")]
    [string]$Action = "optimize-current",
    
    [Parameter(Mandatory=$false)]
    [string]$BackupSuffix = (Get-Date -Format "yyyyMMdd_HHmmss")
)

Write-Host "=== Oracle连接优化实施脚本 ===" -ForegroundColor Green
Write-Host "执行操作: $Action" -ForegroundColor Yellow
Write-Host "备份后缀: $BackupSuffix" -ForegroundColor Yellow
Write-Host ""

# 检查当前目录
if (-not (Test-Path "Lzfy_His_Service.csproj")) {
    Write-Error "请在项目根目录下运行此脚本"
    exit 1
}

# 备份关键文件
function Backup-Files {
    Write-Host "📁 备份关键文件..." -ForegroundColor Blue
    
    $filesToBackup = @(
        "appsettings.json",
        "Services/DatabaseConfigurationService.cs",
        "Services/ResilientOracleConnectionFactory.cs",
        "Lzfy_His_Service.csproj"
    )
    
    foreach ($file in $filesToBackup) {
        if (Test-Path $file) {
            $backupFile = "$file.backup_$BackupSuffix"
            Copy-Item $file $backupFile
            Write-Host "  ✓ 已备份: $file -> $backupFile" -ForegroundColor Green
        }
    }
}

# 优化当前Oracle.ManagedDataAccess.Core配置
function Optimize-CurrentDriver {
    Write-Host "🔧 优化当前Oracle驱动配置..." -ForegroundColor Blue
    
    # 1. 更新appsettings.json中的连接字符串
    Write-Host "  📝 更新appsettings.json连接字符串..." -ForegroundColor Yellow
    
    $appsettingsPath = "appsettings.json"
    if (Test-Path $appsettingsPath) {
        $appsettings = Get-Content $appsettingsPath | ConvertFrom-Json
        
        # 优化Oracle连接字符串
        $optimizedOracleConnection = "Data Source=10.3.255.250:15521/HISDB;User Id=his_user;Password=his_password;Connection Timeout=60;Pooling=true;Min Pool Size=2;Max Pool Size=20;Connection Lifetime=600;Incr Pool Size=2;Decr Pool Size=1;Validate Connection=true;Load Balancing=true;Self Tuning=true;Statement Cache Purge=false;Statement Cache Size=20;Persist Security Info=false;"
        
        $appsettings.ConnectionStrings.HisOracleConnection = $optimizedOracleConnection
        
        $appsettings | ConvertTo-Json -Depth 10 | Set-Content $appsettingsPath
        Write-Host "    ✓ 已更新Oracle连接字符串" -ForegroundColor Green
    }
    
    # 2. 创建增强的重试机制
    Write-Host "  🔄 创建增强的重试机制..." -ForegroundColor Yellow
    
    $enhancedFactoryContent = @'
using System.Data;
using Oracle.ManagedDataAccess.Client;
using Microsoft.Extensions.Logging;
using Polly;
using Polly.CircuitBreaker;
using Polly.Retry;
using Polly.Timeout;
using System.Diagnostics;

namespace Lzfy_His_Service.Services
{
    /// <summary>
    /// 增强的弹性Oracle连接工厂
    /// </summary>
    public class EnhancedResilientOracleConnectionFactory : IOracleConnectionFactory
    {
        private readonly ResiliencePipeline _retryPipeline;
        private readonly string _connectionString;
        private readonly ILogger<EnhancedResilientOracleConnectionFactory> _logger;
        
        public EnhancedResilientOracleConnectionFactory(string connectionString, ILogger<EnhancedResilientOracleConnectionFactory> logger)
        {
            _connectionString = connectionString;
            _logger = logger;
            
            _retryPipeline = new ResiliencePipelineBuilder()
                .AddRetry(new RetryStrategyOptions
                {
                    ShouldHandle = new PredicateBuilder().Handle<OracleException>(IsTransientError),
                    MaxRetryAttempts = 5,
                    Delay = TimeSpan.FromSeconds(1),
                    BackoffType = DelayBackoffType.Exponential,
                    MaxDelay = TimeSpan.FromSeconds(30),
                    OnRetry = args =>
                    {
                        _logger.LogWarning("Oracle连接失败，将在 {Delay}ms 后进行第 {AttemptNumber} 次重试. 错误: {Exception}", 
                            args.RetryDelay.TotalMilliseconds, args.AttemptNumber, args.Outcome.Exception?.Message);
                        return ValueTask.CompletedTask;
                    }
                })
                .AddCircuitBreaker(new CircuitBreakerStrategyOptions
                {
                    FailureRatio = 0.5,
                    SamplingDuration = TimeSpan.FromSeconds(30),
                    MinimumThroughput = 5,
                    BreakDuration = TimeSpan.FromSeconds(60),
                    OnOpened = args =>
                    {
                        _logger.LogError("Oracle连接断路器已打开，暂停连接尝试60秒");
                        return ValueTask.CompletedTask;
                    },
                    OnClosed = args =>
                    {
                        _logger.LogInformation("Oracle连接断路器已关闭，恢复正常连接");
                        return ValueTask.CompletedTask;
                    }
                })
                .AddTimeout(TimeSpan.FromSeconds(90))
                .Build();
        }
        
        public IDbConnection CreateConnection()
        {
            var connection = new OracleConnection(_connectionString);
            connection.InfoMessage += (sender, args) =>
            {
                _logger.LogInformation("Oracle信息消息: {Message}", args.Message);
            };
            return connection;
        }
        
        public async Task<IDbConnection> CreateAndOpenConnectionAsync()
        {
            return await _retryPipeline.ExecuteAsync(async _ =>
            {
                var stopwatch = Stopwatch.StartNew();
                var connection = CreateConnection() as OracleConnection;
                if (connection == null)
                {
                    throw new InvalidOperationException("无法创建 Oracle 连接");
                }
                
                await connection.OpenAsync();
                stopwatch.Stop();
                
                _logger.LogInformation("Oracle连接成功建立，耗时: {ElapsedMs}ms", stopwatch.ElapsedMilliseconds);
                return connection;
            });
        }
        
        private static bool IsTransientError(OracleException ex)
        {
            var transientErrorNumbers = new[]
            {
                // 网络相关错误
                12170, 12171, 12224, 12225, 12514, 12518, 12519, 12520, 12521, 12522, 12523, 12524, 12525, 12526, 12527, 12528, 12529, 12530, 12531, 12532, 12533, 12534, 12535,
                
                // 连接相关错误
                3113, 3114, 1012, 1033, 1034, 1089, 1090,
                
                // 超时和资源相关
                60, 4020, 4021, 30006, 24756, 25408, 29276, 24347
            };
            
            return transientErrorNumbers.Contains(ex.Number);
        }
    }
}
'@
    
    $enhancedFactoryPath = "Services/EnhancedResilientOracleConnectionFactory.cs"
    $enhancedFactoryContent | Set-Content $enhancedFactoryPath -Encoding UTF8
    Write-Host "    ✓ 已创建增强的重试机制" -ForegroundColor Green
    
    # 3. 创建连接字符串优化工具
    Write-Host "  ⚙️ 创建连接字符串优化工具..." -ForegroundColor Yellow
    
    $optimizerContent = @'
using Oracle.ManagedDataAccess.Client;

namespace Lzfy_His_Service.Services
{
    /// <summary>
    /// Oracle连接字符串优化工具
    /// </summary>
    public static class OracleConnectionStringOptimizer
    {
        /// <summary>
        /// 构建优化的Oracle连接字符串
        /// </summary>
        public static string BuildOptimizedConnectionString(string dataSource, string userId, string password)
        {
            var builder = new OracleConnectionStringBuilder
            {
                DataSource = dataSource,
                UserID = userId,
                Password = password,
                
                // 连接超时优化
                ConnectionTimeout = 60,
                
                // 连接池优化
                Pooling = true,
                MinPoolSize = 2,
                MaxPoolSize = 20,
                ConnectionLifeTime = 600,
                IncrPoolSize = 2,
                DecrPoolSize = 1,
                
                // 网络优化
                ValidateConnection = true,
                LoadBalancing = true,
                
                // 性能优化
                SelfTuning = true,
                StatementCachePurge = false,
                StatementCacheSize = 20,
                
                // 安全优化
                PersistSecurityInfo = false
            };
            
            return builder.ConnectionString;
        }
        
        /// <summary>
        /// 从现有连接字符串优化
        /// </summary>
        public static string OptimizeExistingConnectionString(string existingConnectionString)
        {
            var builder = new OracleConnectionStringBuilder(existingConnectionString);
            
            // 应用优化参数
            builder.ConnectionTimeout = Math.Max(builder.ConnectionTimeout, 60);
            builder.Pooling = true;
            builder.MinPoolSize = Math.Max(builder.MinPoolSize, 2);
            builder.MaxPoolSize = Math.Min(builder.MaxPoolSize == 0 ? 20 : builder.MaxPoolSize, 20);
            builder.ConnectionLifeTime = Math.Max(builder.ConnectionLifeTime, 600);
            builder.ValidateConnection = true;
            builder.SelfTuning = true;
            builder.StatementCacheSize = Math.Max(builder.StatementCacheSize, 20);
            builder.PersistSecurityInfo = false;
            
            return builder.ConnectionString;
        }
    }
}
'@
    
    $optimizerPath = "Services/OracleConnectionStringOptimizer.cs"
    $optimizerContent | Set-Content $optimizerPath -Encoding UTF8
    Write-Host "    ✓ 已创建连接字符串优化工具" -ForegroundColor Green
}

# 添加Entity Framework Core支持
function Add-EFCoreSupport {
    Write-Host "📦 添加Entity Framework Core支持..." -ForegroundColor Blue
    
    # 1. 添加NuGet包
    Write-Host "  📥 添加NuGet包..." -ForegroundColor Yellow
    
    $packages = @(
        "Oracle.EntityFrameworkCore",
        "Microsoft.EntityFrameworkCore.Design"
    )
    
    foreach ($package in $packages) {
        Write-Host "    正在添加: $package" -ForegroundColor Gray
        dotnet add package $package
        if ($LASTEXITCODE -eq 0) {
            Write-Host "    ✓ 已添加: $package" -ForegroundColor Green
        } else {
            Write-Host "    ❌ 添加失败: $package" -ForegroundColor Red
        }
    }
    
    # 2. 创建DbContext
    Write-Host "  🗄️ 创建DbContext..." -ForegroundColor Yellow
    
    $dbContextContent = @'
using Microsoft.EntityFrameworkCore;
using Lzfy_His_Service.Models;

namespace Lzfy_His_Service.Data
{
    /// <summary>
    /// HIS系统数据库上下文
    /// </summary>
    public class HisDbContext : DbContext
    {
        public HisDbContext(DbContextOptions<HisDbContext> options) : base(options) { }
        
        // 添加您的实体集
        // public DbSet<PatientInfo> Patients { get; set; }
        
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                // 这里会被Program.cs中的配置覆盖
            }
        }
        
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            
            // 配置实体映射
            // modelBuilder.Entity<PatientInfo>(entity =>
            // {
            //     entity.ToTable("PATIENT_INFO");
            //     entity.HasKey(e => e.PatientId);
            //     entity.Property(e => e.PatientId).HasColumnName("PATIENT_ID");
            // });
        }
    }
}
'@
    
    New-Item -ItemType Directory -Force -Path "Data" | Out-Null
    $dbContextPath = "Data/HisDbContext.cs"
    $dbContextContent | Set-Content $dbContextPath -Encoding UTF8
    Write-Host "    ✓ 已创建HisDbContext" -ForegroundColor Green
    
    # 3. 创建EF Repository基类
    Write-Host "  🏗️ 创建EF Repository基类..." -ForegroundColor Yellow
    
    $efRepositoryContent = @'
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Polly;
using Lzfy_His_Service.Data;

namespace Lzfy_His_Service.Repositories
{
    /// <summary>
    /// Entity Framework Oracle Repository基类
    /// </summary>
    public abstract class EFOracleRepositoryBase<T> where T : class
    {
        protected readonly HisDbContext _context;
        protected readonly ILogger _logger;
        private readonly ResiliencePipeline _retryPolicy;
        
        protected EFOracleRepositoryBase(HisDbContext context, ILogger logger)
        {
            _context = context;
            _logger = logger;
            
            _retryPolicy = new ResiliencePipelineBuilder()
                .AddRetry(new RetryStrategyOptions
                {
                    ShouldHandle = new PredicateBuilder().Handle<Exception>(),
                    MaxRetryAttempts = 3,
                    Delay = TimeSpan.FromSeconds(1),
                    BackoffType = DelayBackoffType.Exponential,
                    OnRetry = args =>
                    {
                        _logger.LogWarning("Oracle EF操作失败，将在 {Delay}ms 后进行第 {AttemptNumber} 次重试", 
                            args.RetryDelay.TotalMilliseconds, args.AttemptNumber);
                        return ValueTask.CompletedTask;
                    }
                })
                .Build();
        }
        
        protected async Task<TResult> ExecuteWithRetryAsync<TResult>(Func<Task<TResult>> operation)
        {
            return await _retryPolicy.ExecuteAsync(async _ => await operation());
        }
        
        protected async Task ExecuteWithRetryAsync(Func<Task> operation)
        {
            await _retryPolicy.ExecuteAsync(async _ => await operation());
        }
    }
}
'@
    
    $efRepositoryPath = "Repositories/EFOracleRepositoryBase.cs"
    $efRepositoryContent | Set-Content $efRepositoryPath -Encoding UTF8
    Write-Host "    ✓ 已创建EF Repository基类" -ForegroundColor Green
}

# 性能测试
function Run-PerformanceTest {
    Write-Host "🚀 运行性能测试..." -ForegroundColor Blue
    
    # 创建性能测试类
    $performanceTestContent = @'
using System.Diagnostics;
using Oracle.ManagedDataAccess.Client;
using Microsoft.Extensions.Logging;
using Lzfy_His_Service.Services;

namespace Lzfy_His_Service.Tests
{
    /// <summary>
    /// Oracle驱动性能测试
    /// </summary>
    public class OracleDriverPerformanceTest
    {
        private readonly ILogger<OracleDriverPerformanceTest> _logger;
        private readonly string _connectionString;
        
        public OracleDriverPerformanceTest(ILogger<OracleDriverPerformanceTest> logger, string connectionString)
        {
            _logger = logger;
            _connectionString = connectionString;
        }
        
        public async Task<PerformanceResult> TestCurrentDriverAsync(int iterations = 100)
        {
            _logger.LogInformation("开始测试当前Oracle驱动性能，迭代次数: {Iterations}", iterations);
            
            var stopwatch = Stopwatch.StartNew();
            var successCount = 0;
            var errors = new List<Exception>();
            var connectionTimes = new List<long>();
            
            for (int i = 0; i < iterations; i++)
            {
                try
                {
                    var connectionStopwatch = Stopwatch.StartNew();
                    
                    using var connection = new OracleConnection(_connectionString);
                    await connection.OpenAsync();
                    
                    // 执行简单查询
                    using var command = connection.CreateCommand();
                    command.CommandText = "SELECT 1 FROM DUAL";
                    await command.ExecuteScalarAsync();
                    
                    connectionStopwatch.Stop();
                    connectionTimes.Add(connectionStopwatch.ElapsedMilliseconds);
                    successCount++;
                }
                catch (Exception ex)
                {
                    errors.Add(ex);
                    _logger.LogWarning("第 {Iteration} 次测试失败: {Error}", i + 1, ex.Message);
                }
            }
            
            stopwatch.Stop();
            
            var result = new PerformanceResult
            {
                TotalTime = stopwatch.Elapsed,
                AverageTime = connectionTimes.Count > 0 ? TimeSpan.FromMilliseconds(connectionTimes.Average()) : TimeSpan.Zero,
                MinTime = connectionTimes.Count > 0 ? TimeSpan.FromMilliseconds(connectionTimes.Min()) : TimeSpan.Zero,
                MaxTime = connectionTimes.Count > 0 ? TimeSpan.FromMilliseconds(connectionTimes.Max()) : TimeSpan.Zero,
                SuccessRate = (double)successCount / iterations,
                ErrorCount = errors.Count,
                Errors = errors,
                Iterations = iterations,
                SuccessCount = successCount
            };
            
            _logger.LogInformation("性能测试完成: 成功率 {SuccessRate:P2}, 平均时间 {AverageTime}ms", 
                result.SuccessRate, result.AverageTime.TotalMilliseconds);
            
            return result;
        }
    }
    
    public class PerformanceResult
    {
        public TimeSpan TotalTime { get; set; }
        public TimeSpan AverageTime { get; set; }
        public TimeSpan MinTime { get; set; }
        public TimeSpan MaxTime { get; set; }
        public double SuccessRate { get; set; }
        public int ErrorCount { get; set; }
        public List<Exception> Errors { get; set; } = new();
        public int Iterations { get; set; }
        public int SuccessCount { get; set; }
    }
}
'@
    
    New-Item -ItemType Directory -Force -Path "Tests" | Out-Null
    $performanceTestPath = "Tests/OracleDriverPerformanceTest.cs"
    $performanceTestContent | Set-Content $performanceTestPath -Encoding UTF8
    Write-Host "    ✓ 已创建性能测试类" -ForegroundColor Green
    
    # 运行编译测试
    Write-Host "  🔨 编译测试..." -ForegroundColor Yellow
    dotnet build --configuration Release
    
    if ($LASTEXITCODE -eq 0) {
        Write-Host "    ✓ 编译成功" -ForegroundColor Green
    } else {
        Write-Host "    ❌ 编译失败" -ForegroundColor Red
        return
    }
}

# 主执行逻辑
switch ($Action) {
    "optimize-current" {
        Backup-Files
        Optimize-CurrentDriver
        Write-Host "✅ 当前Oracle驱动优化完成" -ForegroundColor Green
    }
    
    "add-ef-core" {
        Backup-Files
        Add-EFCoreSupport
        Write-Host "✅ Entity Framework Core支持添加完成" -ForegroundColor Green
    }
    
    "performance-test" {
        Run-PerformanceTest
        Write-Host "✅ 性能测试准备完成" -ForegroundColor Green
    }
    
    "all" {
        Backup-Files
        Optimize-CurrentDriver
        Add-EFCoreSupport
        Run-PerformanceTest
        Write-Host "✅ 所有优化操作完成" -ForegroundColor Green
    }
}

Write-Host ""
Write-Host "📋 后续步骤:" -ForegroundColor Blue
Write-Host "1. 检查生成的文件和配置" -ForegroundColor Gray
Write-Host "2. 根据需要调整连接字符串参数" -ForegroundColor Gray
Write-Host "3. 运行测试验证功能" -ForegroundColor Gray
Write-Host "4. 部署到测试环境" -ForegroundColor Gray
Write-Host "5. 监控性能和稳定性" -ForegroundColor Gray
Write-Host ""
Write-Host "📖 参考文档: Oracle连接框架对比和替代方案.md" -ForegroundColor Blue
Write-Host "🔧 配置脚本: configure-oracle-environment.ps1" -ForegroundColor Blue
Write-Host "🔍 检查脚本: check-oracle-client-environment.ps1" -ForegroundColor Blue